#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
模型18：单一销方单位连号模型

主要功能：
1. 按照【购方单位】和【销方单位】名称相同分组
2. 分析每组内的【发票号码】，筛选出连号的记录
3. 根据条件判断一级/二级风险
"""

import pandas as pd
from pathlib import Path

# 配置常量
DATA_DIR = Path("data")
OUTPUT_DIR = DATA_DIR / "alldata"  # 输出目录
EXPENSE_FILE = DATA_DIR / "费用报销票据信息.xlsx"
OUTPUT_FILE = OUTPUT_DIR / "单一销方单位连号模型分析结果.xlsx"

def load_data():
    """
    加载数据并进行基础处理
    
    Returns:
        DataFrame: 处理后的数据
    """
    # 确保输出目录存在
    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    
    try:
        # 读取费用数据
        df = pd.read_excel(EXPENSE_FILE)
        return df
    except Exception as e:
        print(f"读取数据时出错: {str(e)}")
        raise

def is_consecutive(numbers):
    """
    判断一组数字是否连续
    
    Args:
        numbers: 数字列表
    Returns:
        bool: 是否连续
    """
    numbers = sorted(numbers)
    return all(numbers[i] + 1 == numbers[i + 1] for i in range(len(numbers) - 1))

def analyze_consecutive_invoices(df):
    """
    分析发票连号情况
    
    Args:
        df: DataFrame 原始数据
        
    Returns:
        DataFrame: 分析结果
    """
    try:
        # 先将业务类型转换为字符串，并保持4位数格式（补零）
        df['业务类型'] = df['业务类型'].astype(str).str.zfill(4)
        # 0. 筛选业务类型为0015的记录
        df = df[df['业务类型'].str.strip() == '0015'].copy()        
        if df.empty:
            print("警告：筛选业务类型为0015后没有符合条件的记录")
            return pd.DataFrame()

        # 1. 确保发票号码为数值型
        df['发票号码'] = pd.to_numeric(df['发票号码'], errors='coerce')
        df = df.dropna(subset=['发票号码'])
        
        # 2. 按购方单位和销方单位分组
        groups = df.groupby(['购方单位', '销方单位'])
        
        # 存储结果
        consecutive_records = []
        
        # 3. 分析每组内的发票号码
        for (buyer, seller), group in groups:
            invoice_numbers = sorted(group['发票号码'].unique())
            
            # 如果组内发票少于2张，跳过
            if len(invoice_numbers) < 2:
                continue
                
            # 查找连续的发票号码
            for i in range(len(invoice_numbers) - 1):
                if invoice_numbers[i] + 1 == invoice_numbers[i + 1]:
                    # 获取这两个连续号码的完整记录
                    consecutive_pair = group[group['发票号码'].isin([invoice_numbers[i], invoice_numbers[i + 1]])]
                    consecutive_records.append(consecutive_pair)
        
        if not consecutive_records:
            print("警告：没有找到连号的发票记录")
            return pd.DataFrame()
            
        # 合并所有找到的连号记录
        result = pd.concat(consecutive_records, ignore_index=True)
        
        # 4. 添加风险等级（保留原有逻辑）
        result['风险等级'] = '一级'
        result['发票金额等级'] = '一级'
        
        # 处理每组的风险等级
        for (buyer, seller), group in result.groupby(['购方单位', '销方单位']):
            # 检查销方单位名称是否包含"酒店"
            if seller and '酒店' in seller:
                result.loc[group.index, '风险等级'] = '二级'
            
            # 处理每张发票的金额等级
            amounts = group['发票含税金额']
            for idx in group.index:
                amount = result.loc[idx, '发票含税金额']
                if (700 <= amount <= 800) or (980 <= amount <= 999):
                    result.loc[idx, '发票金额等级'] = '二级'
            
            # 检查两张发票的情况
            if len(group) == 2:
                if all(amounts < 1000):
                    diff = abs(amounts.iloc[1] - amounts.iloc[0])
                    if diff < 100:
                        result.loc[group.index, '发票金额等级'] = '二级'
        # 按发票号码排序
        result = result.sort_values(['购方单位', '销方单位', '发票号码'])
        
        return result
        
    except Exception as e:
        print(f"分析数据时出错: {str(e)}")
        raise

def main(once = False):
    """主程序入口"""
    try:
        print("开始加载数据...")
        df = load_data()
        
        if df.empty:
            print("错误：输入数据为空")
            return False
            
        print("正在分析连号发票...")
        result = analyze_consecutive_invoices(df)

        if once:
            return result
        
        if result.empty:
            print("没有找到符合条件的数据")
            return False
        
        # 保存结果
        with pd.ExcelWriter(OUTPUT_FILE, engine='openpyxl', mode='w') as writer:
            result.to_excel(writer, index=False)
            # 设置业务类型列为文本格式
            if '业务类型' in result.columns:
                worksheet = writer.sheets['Sheet1']
                for idx, col in enumerate(result.columns):
                    if col == '业务类型':
                        # 设置整列为文本格式
                        col_letter = chr(65 + idx)  # 将列索引转换为Excel列字母
                        worksheet.column_dimensions[col_letter].number_format = '@'
                        # 设置每个单元格为文本格式
                        for row in range(2, len(result) + 2):  # Excel是1-based，第一行是标题
                            cell = worksheet.cell(row=row, column=idx + 1)
                            cell.number_format = '@'  # 设置单元格格式为文本
                            # 确保单元格的值是字符串格式
                            cell.value = str(cell.value).zfill(4)
        
        print(f"\n分析完成，结果已保存至: {OUTPUT_FILE}")
        print(f"筛选出的记录数: {len(result)}")
        
        # 统计分析
        print("\n数据统计:")
        print(f"一级风险记录数: {len(result[result['风险等级'] == '一级'])}")
        print(f"二级风险记录数: {len(result[result['风险等级'] == '二级'])}")
        print(f"发票一级记录数: {len(result[result['发票金额等级'] == '一级'])}")
        print(f"发票二级记录数: {len(result[result['发票金额等级'] == '二级'])}")
        
        return True
        
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}")
        raise

if __name__ == "__main__":
    main() 